package com.example.teacherservice.repository;

import com.example.teacherservice.dto.ClassInfo;
import com.example.teacherservice.dto.StudentClassInfo;
import com.example.teacherservice.entity.StuClass;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Repository
public interface StuClassRepository extends JpaRepository<StuClass, Long> {

    List<StuClass> findByClassId(Long classId);

    boolean existsByStuIdAndClassId(Long stuId, Long classId);

    @Modifying
    @Transactional
    @Query("DELETE FROM StuClass sc WHERE sc.stuId = :stuId AND sc.classId = :classId")
    int deleteByStuIdAndClassId(@Param("stuId") Long stuId, @Param("classId") Long classId);

    // 批量保存方法
    @Modifying
    @Transactional
    @Query(value = "INSERT INTO stu_class (stu_id, class_id, create_time, update_time) " +
            "VALUES (?1, ?2, NOW(), NOW()) ON DUPLICATE KEY UPDATE update_time = NOW()",
            nativeQuery = true)
    int batchInsert(Long stuId, Long classId);

    // 批量查询是否已存在
    @Query("SELECT sc FROM StuClass sc WHERE sc.classId = :classId AND sc.stuId IN :studentIds")
    List<StuClass> findExistingByClassIdAndStudentIds(@Param("classId") Long classId,
                                                      @Param("studentIds") List<Long> studentIds);

    // 批量删除方法
    @Modifying
    @Transactional
    @Query("DELETE FROM StuClass sc WHERE sc.classId = :classId AND sc.stuId IN :studentIds")
    int batchDeleteByClassIdAndStudentIds(@Param("classId") Long classId,
                                          @Param("studentIds") List<Long> studentIds);


    // 修改为直接查询班级名称和ID
    @Query("SELECT new com.example.teacherservice.dto.ClassInfo(c.id, c.className) " +
            "FROM StuClass sc JOIN Class c ON sc.classId = c.id " +
            "WHERE sc.stuId = :studentId")
    List<ClassInfo> findClassesByStudentId(@Param("studentId") Long studentId);


    @Modifying
    @Transactional
    @Query("DELETE FROM StuClass sc WHERE sc.classId = :classId")
    void deleteByClassId(@Param("classId") Long classId);

    // 可选：添加计数方法用于验证
    @Query("SELECT COUNT(sc) FROM StuClass sc WHERE sc.classId = :classId")
    long countByClassId(@Param("classId") Long classId);

    @Modifying
    @Transactional
    @Query("DELETE FROM StuClass sc WHERE sc.classId = :classId AND sc.stuId = :studentId")
    int deleteByClassIdAndStudentId(@Param("classId") Long classId,
                                    @Param("studentId") Long studentId);

    @Query("SELECT sc.stuId FROM StuClass sc WHERE sc.classId = :classId")
    List<Long> findStudentIdsByClassId(@Param("classId") Long classId);

    @Query("SELECT COUNT(sc) FROM StuClass sc WHERE sc.stuId = :studentId")
    long countByStuId(@Param("studentId") Long studentId);

    @Query("SELECT new com.example.teacherservice.dto.StudentClassInfo(" +
            "c.id, c.className, c.classStuNum, c.createTime) " +
            "FROM StuClass sc JOIN Class c ON sc.classId = c.id " +
            "WHERE sc.stuId = :studentId")
    List<StudentClassInfo> findClassesByStudentId1(@Param("studentId") Long studentId);

}